﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Generico.Coneccion;
using System.Net;
using System.IO;
using System.Text;

namespace Administracion.Administracion
{
    public partial class Cotizaciones : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!this.IsPostBack)
            {
                CargarListaBancos();
                CargarListaMonedas();
                txtFecha.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
            }
            
        }
        private void CargarListaBancos()
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            var rd = cn.EjecutarSpDataReader("sp_ADM_GetBancos");
            var dt = new DataTable();
            dt.Load(rd);
            ddropBanco.DataSource = dt;
            ddropBanco.DataTextField = "varBanco";
            ddropBanco.DataValueField = "idBanco";
            ddropBanco.DataBind();
            cn.Cerrar();
        }
        private void CargarListaMonedas()
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            var rd = cn.EjecutarSpDataReader("sp_ADM_GetMonedas");
            var dt = new DataTable();
            dt.Load(rd);
            ddropMoneda.DataSource = dt;
            ddropMoneda.DataTextField = "varMoneda";
            this.ddropMoneda.DataValueField = "idMoneda";
            this.ddropMoneda.DataBind();
            cn.Cerrar();
        }
       
        protected void btnAceptar_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idBanco", Convert.ToInt32(ddropBanco.SelectedValue));
            cn.AgregarParametros("idMoneda", Convert.ToInt32(ddropMoneda.SelectedValue));
            cn.AgregarParametros("datFecha", Convert.ToDateTime(txtFecha.Text));
            cn.AgregarParametros("monCompra", Convert.ToDecimal(txtCompra.Text));
            cn.AgregarParametros("monVenta", Convert.ToDecimal(txtVenta.Text));
            cn.EjecutarSp("sp_ADM_NewCotizacion");
            cn.Cerrar();
            Response.Write("<script> alert('Se ha guardado correctamente');</script>");
        }

        protected void btnBuscar_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idBanco", Convert.ToInt32(ddropBanco.SelectedValue));
            cn.AgregarParametros("idMoneda", Convert.ToInt32(ddropMoneda.SelectedValue));
            string desde = txtFecha.Text.Substring(6, 4) + txtFecha.Text.Substring(3, 2) + txtFecha.Text.Substring(0, 2);
            
            cn.AgregarParametros("FechaDesde", desde + " 00:00:00");
            cn.AgregarParametros("FechaHasta", desde + " 23:59:59");

            var rd = cn.EjecutarSpDataReader("sp_ADM_GetCotizaciones");
            lstCotizacion.DataTextField = "datFechaIngreso";
            lstCotizacion.DataValueField = "id";
            lstCotizacion.DataSource = rd;
            lstCotizacion.DataBind();
            cn.Cerrar();
            
            
        }

        protected void btnEliminar_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idCotizacion", Convert.ToInt32(lstCotizacion.SelectedValue));
            cn.EjecutarSp("sp_ADM_DeleteCotizaciones");
            cn.Cerrar();
            Response.Write("<script> alert('Se ha eliminado correctamente');</script>");
        }

        protected void btnGenerador_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("intMoneda", Convert.ToInt32(ddropMoneda.SelectedValue));
            cn.AgregarParametros("datFecha", Convert.ToDateTime(txtFecha.Text));
            cn.EjecutarSp("sp_ADM_NewPromedio");
            cn.Cerrar();
            Response.Write("<script> alert('Se ha guardado correctamente');</script>");
        }

        protected void btnBuscarPromedio_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idMoneda", Convert.ToInt32(ddropMoneda.SelectedValue));
            string desde = txtFecha.Text.Substring(6, 4) + txtFecha.Text.Substring(3, 2) + txtFecha.Text.Substring(0, 2);

            cn.AgregarParametros("FechaDesde", desde + " 00:00:00");
            cn.AgregarParametros("FechaHasta", desde + " 23:59:59");

            var rd = cn.EjecutarSpDataReader("sp_ADM_GetPromedioCotizaciones");
            lstCotizacion.DataTextField = "datFechaIngreso";
            lstCotizacion.DataValueField = "id";
            lstCotizacion.DataSource = rd;
            lstCotizacion.DataBind();
            cn.Cerrar();
        }

        protected void btnEliminarPromedio_Click(object sender, EventArgs e)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idCotizacion", Convert.ToInt32(lstCotizacion.SelectedValue));
            cn.EjecutarSp("sp_ADM_DeletePromedioCotizaciones");
            cn.Cerrar();
            Response.Write("<script> alert('Se ha eliminado correctamente');</script>");
        }

        protected void btnActualizarCotizaciones_Click(object sender, EventArgs e)
        {

            try
            {

                var cn = new ClsConeccion();

                cn.Abrir();
                string sql = "DELETE from CotizacionesActuales";
                var sqldr1 = cn.EjecutarSqlDataReader(sql);
                cn.Cerrar();

                cn.Abrir();
                cn.AgregarParametros("intMoneda", 1);
                var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_Actualizar_Cotizaciones_Monedas");  
                cn.Cerrar();

                cn.Abrir();
                cn.AgregarParametros("intMoneda", 2);
                var sqldr3 = cn.EjecutarSpDataReader("sp_ADM_Actualizar_Cotizaciones_Monedas");  
                cn.Cerrar();

                cn.Abrir();
                cn.AgregarParametros("intMoneda", 3);
                var sqldr4 = cn.EjecutarSpDataReader("sp_ADM_Actualizar_Cotizaciones_Monedas");  
                cn.Cerrar();

                Response.Write("<script> alert('Las cotizaciones han sido actualizadas');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("<script> alert('Hubo un error al actualizar las cotizaciones. Mensaje de Error: '" + ex.Message + " );</script>");
                        
            }

        }


        protected void btnActualizarCotizacionesHistoricas_Click(object sender, EventArgs e)
        {

            try
            {               

                var cn = new ClsConeccion();

                var dtCotizHist = new DataTable("CotizacionHistorica");

                var dtColFecha = new DataColumn("Fecha", System.Type.GetType("System.DateTime"));
                var dtColCodigo = new DataColumn("Codigo", System.Type.GetType("System.String"));
                var dtColTipo = new DataColumn("Tipo", System.Type.GetType("System.String"));
                var dtColMoneda = new DataColumn("Moneda", System.Type.GetType("System.String"));
                var dtColValor = new DataColumn("Valor", System.Type.GetType("System.String"));
                var dtColCambioDia = new DataColumn("CambioDia", System.Type.GetType("System.String"));
                var dtColCambioMes = new DataColumn("CambioMes", System.Type.GetType("System.String"));
                var dtColCambioAno = new DataColumn("CambioAno", System.Type.GetType("System.String"));
                var dtColTIR = new DataColumn("TIR", System.Type.GetType("System.String"));

                dtCotizHist.Columns.Add(dtColFecha);
                dtCotizHist.Columns.Add(dtColCodigo);
                dtCotizHist.Columns.Add(dtColTipo);
                dtCotizHist.Columns.Add(dtColMoneda);
                dtCotizHist.Columns.Add(dtColValor);
                dtCotizHist.Columns.Add(dtColCambioDia);
                dtCotizHist.Columns.Add(dtColCambioMes);
                dtCotizHist.Columns.Add(dtColCambioAno);
                dtCotizHist.Columns.Add(dtColTIR);

                var fechaActualización = new DateTime();

                if (DateTime.Now.DayOfWeek == DayOfWeek.Monday && DateTime.Now.Hour < 10)
                    fechaActualización = DateTime.Now.AddDays(-3);
                else
                {
                    if (DateTime.Now.DayOfWeek == DayOfWeek.Sunday)
                        fechaActualización = DateTime.Now.AddDays(-2);
                    else
                    {
                        if (DateTime.Now.DayOfWeek == DayOfWeek.Saturday)
                            fechaActualización = DateTime.Now.AddDays(-1);
                        else
                        {
                            if (DateTime.Now.Hour < 10)
                                fechaActualización = DateTime.Now.AddDays(-1);
                            else
                                fechaActualización = DateTime.Now;
                        }
                    }
                }


                cn.Abrir();
                string sqlVerificacion = "SELECT COUNT(1) FROM HistoricoCotizaciones WHERE Fecha = convert(datetime,'" + fechaActualización.ToShortDateString() + "', 105)";

                var sqldrVerificacion = cn.EjecutarSqlDataReader(sqlVerificacion);
                var dtVerificacion = new DataTable();
                dtVerificacion.Load(sqldrVerificacion);

                cn.Cerrar();


                if (Convert.ToInt32(dtVerificacion.Rows[0][0]) > 0)
                {

                    Response.Write("<script> alert('Las cotizaciones del día " + fechaActualización.ToShortDateString() + " ya han sido actualizadas. No es posible volver a hacerlo');</script>");

                }
                else
                {
                    if (DateTime.Now.Hour >= 10 && DateTime.Now.Hour < 18 && DateTime.Now.DayOfWeek != DayOfWeek.Saturday && DateTime.Now.DayOfWeek != DayOfWeek.Sunday)
                    {
                        Response.Write("<script> alert('Solo es posible actualizar las cotizaciones antes de las 10 hs y luego de las 18 hs. Actualmente los mercados están abiertos');</script>");

                    }
                    else
                    {

                        //        ----------------    MONEDAS   ---------------------

                        cn.Abrir();
                        string sql = "Select varMoneda, Replace (Cast(Cast(PromedioRFCompra as Numeric(9,3)) as varchar),'.',',')  Compra, Replace (Cast(Cast(PromedioRFVenta as Numeric(9,3)) as varchar),'.',',')  Venta From Promedios P ";
                        sql += "inner Join (Select Moneda, Max(Fecha) fec From Promedios ";
                        sql += "group by Moneda) P1 on P1.Moneda = P.Moneda and P1.Fec = P.Fecha ";
                        sql += "Inner Join Monedas M on P.Moneda = M.idMoneda order by P.Moneda ";

                        var sqldr2 = cn.EjecutarSqlDataReader(sql);
                        var dt = new DataTable();
                        dt.Load(sqldr2);

                        cn.Cerrar();

                        foreach (DataRow dr in dt.Rows)
                        {

                            var drowMonedas = dtCotizHist.NewRow();

                            drowMonedas["Fecha"] = fechaActualización.ToShortDateString();
                            drowMonedas["Tipo"] = "Moneda";
                            drowMonedas["Moneda"] = DBNull.Value;
                            drowMonedas["CambioDia"] = DBNull.Value;
                            drowMonedas["CambioMes"] = DBNull.Value;
                            drowMonedas["CambioAno"] = DBNull.Value;
                            drowMonedas["TIR"] = DBNull.Value;

                            switch (dr["varMoneda"].ToString())
                            {
                                case "DÓLAR":
                                    drowMonedas["Codigo"] = "Dólar Promedio RF";
                                    drowMonedas["Valor"] = Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString();
                                    break;

                                case "EURO":
                                    drowMonedas["Codigo"] = "Euro Promedio RF";
                                    drowMonedas["Valor"] = Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString();
                                    break;

                                case "REAL":
                                    drowMonedas["Codigo"] = "Real Promedio RF";
                                    drowMonedas["Valor"] = Math.Round(Convert.ToDecimal(dr["Venta"]), 2).ToString();
                                    break;

                                default:
                                    break;
                            }

                            dtCotizHist.Rows.Add(drowMonedas);
                        }

                        cn.Abrir();

                        string sqlBlue = "SELECT * from CotizacionesActuales WHERE intOrden = 99999 AND intBanco = 29";

                        var sqldrBlue = cn.EjecutarSqlDataReader(sqlBlue);

                        var dtBlue = new DataTable();

                        dtBlue.Load(sqldrBlue);

                        if (dtBlue.Rows.Count > 0)
                        {

                            var drowBlue = dtCotizHist.NewRow();

                            drowBlue["Fecha"] = fechaActualización.ToShortDateString();
                            drowBlue["Tipo"] = "Moneda";
                            drowBlue["Moneda"] = DBNull.Value;
                            drowBlue["CambioDia"] = DBNull.Value;
                            drowBlue["CambioMes"] = DBNull.Value;
                            drowBlue["CambioAno"] = DBNull.Value;
                            drowBlue["TIR"] = DBNull.Value;
                            drowBlue["Codigo"] = "Dólar Blue Ambito.com";

                            string temp = dtBlue.Rows[0]["monVenta"].ToString().Replace("$ ", "");
                            string temp2 = temp.Replace("$", "");

                            drowBlue["Valor"] = Math.Round(Convert.ToDecimal(temp2), 2).ToString();

                            dtCotizHist.Rows.Add(drowBlue);

                        }

                        cn.Cerrar();

                        // Dolar MEP
                        var drowMEP = dtCotizHist.NewRow();

                        drowMEP["Fecha"] = fechaActualización.ToShortDateString();
                        drowMEP["Tipo"] = "Moneda";
                        drowMEP["Moneda"] = DBNull.Value;
                        drowMEP["CambioDia"] = DBNull.Value;
                        drowMEP["CambioMes"] = DBNull.Value;
                        drowMEP["CambioAno"] = DBNull.Value;
                        drowMEP["TIR"] = DBNull.Value;
                        drowMEP["Codigo"] = "Dólar MEP";

                        drowMEP["Valor"] = ObtenerCotizacionDolarMEP();

                        dtCotizHist.Rows.Add(drowMEP);

                        // Dolar A 3500
                        var drowA3500 = dtCotizHist.NewRow();

                        drowA3500["Fecha"] = fechaActualización.ToShortDateString();
                        drowA3500["Tipo"] = "Moneda";
                        drowA3500["Moneda"] = DBNull.Value;
                        drowA3500["CambioDia"] = DBNull.Value;
                        drowA3500["CambioMes"] = DBNull.Value;
                        drowA3500["CambioAno"] = DBNull.Value;
                        drowA3500["TIR"] = DBNull.Value;
                        drowA3500["Codigo"] = "Dólar A 3500";

                        drowA3500["Valor"] = ObtenerCotizacionDolarA3500();

                        dtCotizHist.Rows.Add(drowA3500);

                        // Dolar CCL
                        var drowCCL = dtCotizHist.NewRow();

                        drowCCL["Fecha"] = fechaActualización.ToShortDateString();
                        drowCCL["Tipo"] = "Moneda";
                        drowCCL["Moneda"] = DBNull.Value;
                        drowCCL["CambioDia"] = DBNull.Value;
                        drowCCL["CambioMes"] = DBNull.Value;
                        drowCCL["CambioAno"] = DBNull.Value;
                        drowCCL["TIR"] = DBNull.Value;
                        drowCCL["Codigo"] = "Dólar CCL";

                        drowCCL["Valor"] = ObtenerCotizacionCCL();

                        dtCotizHist.Rows.Add(drowCCL);


                        //        ----------------    INDICES   ---------------------


                        // Obtener último día hábil del año anterior
                        var lastMarketDayOfLastYear = new DateTime(DateTime.Now.Year - 1, 12, 31);

                        if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Saturday)
                        {
                            lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-1);
                        }
                        else
                        {
                            if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Sunday)
                            {
                                lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-2);
                            }
                        }


                        // Obtener primer día hábil del mes
                        var firstMarketDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                        var lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-1);


                        if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Monday)
                        {
                            lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-3);
                        }
                        else
                        {
                            if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Sunday)
                            {
                                lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-2);
                            }

                        }
                        
                        List<Decimal> valueFirstMarketDayOfYear = new List<decimal>();
                        List<Decimal> valueFirstMarketDayOfMonth = new List<decimal>();

                        // ---------------------------------- Indices desde Google -----------------------------------

                        string symbolsGoogle = "INDEXDJX:.DJI,SHA:000001,INDEXBME:IB";
                        string symbolsYahoo = "^DJI,^SSEC,^IBEX";

                        string[] YahooSymbolsSplit = symbolsYahoo.Replace(",", " ").Split(' ');


                        // Verifica si hoy es 1 de Enero
                        if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                        {

                            // Obtener las cotizaciones del primer dia del año

                            cn.Abrir();
                            cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                            cn.AgregarParametros("tipo", "Indice");
                            var sqldrCotizacionesfirstMarketDayOfYear = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                            var dtCotizacionesfirstMarketDayOfYear = new DataTable();
                            dtCotizacionesfirstMarketDayOfYear.Load(sqldrCotizacionesfirstMarketDayOfYear);
                            cn.Cerrar();

                            for (int i = 0; i < YahooSymbolsSplit.Length; i++)
                            {

                                foreach (DataRow item in dtCotizacionesfirstMarketDayOfYear.Rows)
                                {

                                    if (YahooSymbolsSplit[i].Trim() == item["Codigo"].ToString())
                                    {
                                        valueFirstMarketDayOfYear.Add(Convert.ToDecimal(item["Cotizacion"]));
                                        break;
                                    }
                                }
                            }
                        }


                    if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                    {

                        // Obtener las cotizaciones del primer dia del Mes. 
                    
                        cn.Abrir();
                        cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                        cn.AgregarParametros("tipo", "Indice");
                        var sqldrCotizacionesfirstMarketDayOfMonth = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                        var dtCotizacionesfirstMarketDayOfMonth = new DataTable();
                        dtCotizacionesfirstMarketDayOfMonth.Load(sqldrCotizacionesfirstMarketDayOfMonth);
                        cn.Cerrar();


                        for (int i = 0; i < YahooSymbolsSplit.Length; i++)
                        {

                            foreach (DataRow item in dtCotizacionesfirstMarketDayOfMonth.Rows)
                            {

                                if (YahooSymbolsSplit[i].Trim() == item["Codigo"].ToString())
                                {
                                    valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(item["Cotizacion"]));
                                    break;
                                }
                            }
                        }
                    }


                    // Use Yahoo finance service to download stock data from Yahoo
                    string googleURL = @"http://www.google.com/finance/info?q=" + symbolsGoogle; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                    // Initialize a new WebRequest.
                    HttpWebRequest webreqGoogleFinance = (HttpWebRequest)WebRequest.Create(googleURL);
                    // Get the response from the Internet resource.
                    HttpWebResponse webrespGoogleFinance = (HttpWebResponse)webreqGoogleFinance.GetResponse();
                    // Read the body of the response from the server.
                    StreamReader strmGoogleFinance = new StreamReader(webrespGoogleFinance.GetResponseStream(), Encoding.ASCII);

                    string content = strmGoogleFinance.ReadToEnd();
                    string content2 = content.Replace("\n", "").Replace("\"", "").Replace("// [{", "").Replace("{", "").Replace("}", "").Replace("[", "").Replace("]", "").Replace(",id", "").Replace(",l_fix", "");
                    
                    string[] contentArray = content2.ToString().Split(':');

                    string[] GoogleSymbolsSplit = symbolsGoogle.Replace(",", " ").Split(' ');

                    int j = 0;

                    for (int i = 0; i < YahooSymbolsSplit.Length; i++)
                    {

                        j += 4;
                        Decimal valueIndex = Convert.ToDecimal(contentArray[j].Trim().Replace(",", "").Replace(".", ","));

                        j += 16;
                        Decimal valueLastDay = Convert.ToDecimal(contentArray[j].Trim().Replace(",", "").Replace(".", ","));

                        var drowGoogleFinance = dtCotizHist.NewRow();

                        drowGoogleFinance["Fecha"] = fechaActualización.ToShortDateString();
                        drowGoogleFinance["Codigo"] = GetCompleteNameByCode(YahooSymbolsSplit[i].Trim());
                        drowGoogleFinance["Tipo"] = "Indice";
                        drowGoogleFinance["Moneda"] = DBNull.Value;
                        drowGoogleFinance["TIR"] = DBNull.Value;
                        drowGoogleFinance["Valor"] = Math.Round(valueIndex, 2);
                        drowGoogleFinance["CambioDia"] = Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2).ToString();


                        if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                            drowGoogleFinance["CambioMes"] = Math.Round(((valueIndex - valueFirstMarketDayOfMonth[i]) / valueFirstMarketDayOfMonth[i]) * 100, 2);
                        else
                            drowGoogleFinance["CambioMes"] = Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2);

                        if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                            drowGoogleFinance["CambioAno"] = Math.Round(((valueIndex - valueFirstMarketDayOfYear[i]) / valueFirstMarketDayOfYear[i]) * 100, 2);
                        else
                            drowGoogleFinance["CambioAno"] = Math.Round(((valueIndex - valueLastDay) / valueLastDay) * 100, 2);


                        dtCotizHist.Rows.Add(drowGoogleFinance);

                    }

             // ---------------------------- FIN INDICES DESDE GOOGLE FINANCE, INICIO INDICES DESDE YAHOO! FINANCE ------------------------

                        GetRowsFromQuotes("^IXIC,^GSPC,^MERV,^BVSP,^FTSE,^GDAXI,^N225", "Indice", fechaActualización, ref dtCotizHist);

                        //        ----------------    ACCIONES   ---------------------
                        GetRowsFromQuotes("ALUA.BA,ERAR.BA,APBR.BA,PBR,TS.BA,TS,YPFD.BA,YPF,PESA.BA,COME.BA,GGAL.BA,GGAL,BMA.BA,BMA,FRAN.BA,BFR,PAMP.BA,PAM,EDN.BA,EDN,TRAN.BA,MOLI.BA,MIRG.BA,TECO2.BA,TEO", "Accion", fechaActualización, ref dtCotizHist);

                        //        ----------------    BONOS   ---------------------

                        string quotesBonos = "";

                        if (DateTime.Now < new DateTime(2017, 04, 17))
                            quotesBonos = "AA17.BA,AA17D.BA,GJ17.BA,GJ17D.BA,AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";
                        else
                        {
                            if (DateTime.Now < new DateTime(2017, 06, 02))
                                quotesBonos = "GJ17.BA,GJ17D.BA,AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";

                            else
                            {
                                if (DateTime.Now < new DateTime(2018, 11, 29))
                                    quotesBonos = "AN18.BA,AN18D.BA,AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";

                                else
                                {

                                    if (DateTime.Now < new DateTime(2020, 10, 04))
                                        quotesBonos = "AO20.BA,AO20D.BA,AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";

                                    else
                                    {

                                        if (DateTime.Now < new DateTime(2024, 05, 07))
                                            quotesBonos = "AY24.BA,AY24D.BA,DICA.BA,DICY.BA,PARA.BA";
                                        else
                                        {
                                            if (DateTime.Now < new DateTime(2033, 12, 31))
                                                quotesBonos = "DIA0.BA,DIA0D.BA,DICA.BA,DICAD.BA,DICY.BA,DICYD.BA,PARA.BA,PARAD.BA";
                                            else
                                            {
                                                if (DateTime.Now < new DateTime(2038, 12, 31))
                                                    quotesBonos = "PARA.BA,PARAD.BA";
                                            }
                                        }
                                    }
                                }
                            }
                        }   

                        GetRowsFromQuotes(quotesBonos, "Bono", fechaActualización, ref dtCotizHist);


                        foreach (DataRow item in dtCotizHist.Rows)
                        {

                            cn.Abrir();
                            cn.AgregarParametros("Fecha", Convert.ToDateTime(item["Fecha"]).ToShortDateString());
                            cn.AgregarParametros("Codigo", item["Codigo"]);
                            cn.AgregarParametros("Tipo", item["Tipo"]);
                            cn.AgregarParametros("Moneda", item["Moneda"]);
                            cn.AgregarParametros("Valor", item["Valor"]);
                            cn.AgregarParametros("CambioDia", item["CambioDia"]);
                            cn.AgregarParametros("CambioMes", item["CambioMes"]);
                            cn.AgregarParametros("CambioAno", item["CambioAno"]);
                            cn.AgregarParametros("TIR", item["TIR"]);

                            var sqldrCotiz = cn.EjecutarSpDataReader("sp_ADM_NewCotizacionHistorica");
                            cn.Cerrar();

                        }

                        Response.Write("<script> alert('Las cotizaciones históricas del día " + fechaActualización.ToShortDateString() + " han sido actualizadas');</script>");
                    }
                }

            }
            catch (Exception ex)
            {
                Response.Write("<script> alert('Hubo un error al actualizar las cotizaciones. Mensaje de error:" + ex.Message + "');</script>");

            }

        }

        private void GetRowsFromQuotes(string quotes, string tipo, DateTime fechaActualizacion, ref DataTable dtCotizaciones)
        {

            try
            {

                var cn = new ClsConeccion();

                // Obtener último día hábil del año anterior
                var lastMarketDayOfLastYear = new DateTime(DateTime.Now.Year - 1, 12, 31);

                if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Saturday)
                {
                    lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-1);
                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, 1, 2).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastYear = lastMarketDayOfLastYear.AddDays(-2);
                    }
                }


                // Obtener primer día hábil del mes
                var firstMarketDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                var lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-1);


                if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Monday)
                {
                    lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-3);
                }
                else
                {
                    if (new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).DayOfWeek == DayOfWeek.Sunday)
                    {
                        lastMarketDayOfLastMonth = firstMarketDayOfMonth.AddDays(-2);
                    }
                }

                string[] symbols = quotes.Replace(",", " ").Split(' ');
                List<Decimal> valueFirstMarketDayOfYear = new List<decimal>();
                List<Decimal> valueFirstMarketDayOfMonth = new List<decimal>();

                List<CashFlow> CuponesDiscount = new List<CashFlow>();
                List<CashFlow> CuponesPar = new List<CashFlow>();

                if (tipo == "Bono")
                {
                    cn.Abrir();
                    cn.AgregarParametros("codigoBono", "DIA0");
                    var sqldrCuponesDIA0 = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                    var dtCuponesDIA0 = new DataTable();
                    dtCuponesDIA0.Load(sqldrCuponesDIA0);
                    cn.Cerrar();

                    for (int i = 0; i < dtCuponesDIA0.Rows.Count; i++)
                    {
                        CuponesDiscount.Add(new CashFlow(Convert.ToDouble(dtCuponesDIA0.Rows[i]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCuponesDIA0.Rows[i]["FechaPago"])));
                    }


                    cn.Abrir();
                    cn.AgregarParametros("codigoBono", "PAA0");
                    var sqldrCuponesPAA0 = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                    var dtCuponesPAA0 = new DataTable();
                    dtCuponesPAA0.Load(sqldrCuponesPAA0);
                    cn.Cerrar();

                    for (int i = 0; i < dtCuponesPAA0.Rows.Count; i++)
                    {
                        CuponesPar.Add(new CashFlow(Convert.ToDouble(dtCuponesPAA0.Rows[i]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCuponesPAA0.Rows[i]["FechaPago"])));
                    }

                }


                if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1) && tipo != "Bono")
                {

                    // Obtener las cotizaciones del primer dia del año. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas

                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", tipo);
                    var sqldrCotizacionesfirstMarketDayOfYear = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfYear = new DataTable();
                    dtCotizacionesfirstMarketDayOfYear.Load(sqldrCotizacionesfirstMarketDayOfYear);
                    cn.Cerrar();

                    if (dtCotizacionesfirstMarketDayOfYear.Rows.Count > 1)
                    {

                        for (int i = 0; i < symbols.Length; i++)
                        {

                            foreach (DataRow item in dtCotizacionesfirstMarketDayOfYear.Rows)
                            {

                                if (symbols[i].Trim() == item["Codigo"].ToString())
                                {
                                    valueFirstMarketDayOfYear.Add(Convert.ToDecimal(item["Cotizacion"]));
                                    break;
                                }
                            }
                        }
                    }
                    else     // las cotizaciones no están en la base de datos. Se las va a buscar a Yahoo! y se las agrega.
                    {
                        for (int i = 0; i < symbols.Length; i++)
                        {
                            // Loop through each line from the stream, building the return XML Document string
                            if (symbols[i].Trim() == "")
                                continue;

                            // Use Yahoo finance service to download stock data from Yahoo
                            string yahooURLFirstMarketDayOfYear = @"http://ichart.finance.yahoo.com/table.csv?s=" + symbols[i].Trim() + "&a=" + lastMarketDayOfLastYear.AddMonths(-1).Month.ToString() + "&b=" + lastMarketDayOfLastYear.Day.ToString() + "&c=" + lastMarketDayOfLastYear.Year.ToString() + "&d=" + lastMarketDayOfLastYear.AddMonths(-1).Month.ToString() + "&e=" + lastMarketDayOfLastYear.Day.ToString() + "&f=" + lastMarketDayOfLastYear.Year.ToString() + "&ignore=.csv";

                            using (WebClient web = new WebClient())
                            {
                                string data = web.DownloadString(yahooURLFirstMarketDayOfYear);

                                data = data.Replace("\n", ",");

                                string[] rows = data.Split(',');

                                valueFirstMarketDayOfYear.Add(Convert.ToDecimal(rows[11].Replace(".", ",").Replace("$ ", "")));

                                cn.Abrir();
                                cn.AgregarParametros("codigo", symbols[i].Trim());
                                cn.AgregarParametros("fecha", lastMarketDayOfLastYear.Date.ToShortDateString());
                                cn.AgregarParametros("cotizacion", rows[11].Replace(".", ",").Replace("$ ", ""));
                                cn.AgregarParametros("tipo", tipo);

                                var sqldrUpdateCotizacion = cn.EjecutarSpDataReader("sp_ADM_NewCotizacionReferencia");

                                cn.Cerrar();

                            }
                        }
                    }
                }


                if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date && tipo != "Bono")
                {

                    // Obtener las cotizaciones del primer dia del Mes. Si no están guardadas, hay que buscarlas en Yahoo!
                    // y grabarlas


                    cn.Abrir();
                    cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                    cn.AgregarParametros("tipo", tipo);
                    var sqldrCotizacionesfirstMarketDayOfMonth = cn.EjecutarSpDataReader("sp_ADM_GetCotizacionByFechaAndTipo");
                    var dtCotizacionesfirstMarketDayOfMonth = new DataTable();
                    dtCotizacionesfirstMarketDayOfMonth.Load(sqldrCotizacionesfirstMarketDayOfMonth);
                    cn.Cerrar();

                    if (dtCotizacionesfirstMarketDayOfMonth.Rows.Count > 1)
                    {

                        for (int i = 0; i < symbols.Length; i++)
                        {

                            foreach (DataRow item in dtCotizacionesfirstMarketDayOfMonth.Rows)
                            {

                                if (symbols[i].Trim() == item["Codigo"].ToString())
                                {
                                    valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(item["Cotizacion"]));
                                    break;
                                }
                            }
                        }
                    }
                    else     // las cotizaciones no están en la base de datos. Se las va a buscar a Yahoo! y se las agrega.
                    {

                        for (int i = 0; i < symbols.Length; i++)
                        {
                            // Loop through each line from the stream, building the return XML Document string
                            if (symbols[i].Trim() == "")
                                continue;


                            // Use Yahoo finance service to download stock data from Yahoo
                            string yahooURLFirstMarketDayOfMonth = @"http://ichart.finance.yahoo.com/table.csv?s=" + symbols[i].Trim() + "&a=" + lastMarketDayOfLastMonth.AddMonths(-1).Month.ToString() + "&b=" + lastMarketDayOfLastMonth.Day.ToString() + "&c=" + lastMarketDayOfLastMonth.Year.ToString() + "&d=" + lastMarketDayOfLastMonth.AddMonths(-1).Month.ToString() + "&e=" + lastMarketDayOfLastMonth.Day.ToString() + "&f=" + lastMarketDayOfLastMonth.Year.ToString() + "&ignore=.csv";

                            using (WebClient web = new WebClient())
                            {
                                string data = web.DownloadString(yahooURLFirstMarketDayOfMonth);

                                data = data.Replace("\n", ",");

                                string[] rows = data.Split(',');

                                valueFirstMarketDayOfMonth.Add(Convert.ToDecimal(rows[11].Replace(".", ",").Replace("$ ", "")));


                                cn.Abrir();
                                cn.AgregarParametros("codigo", symbols[i].Trim());
                                cn.AgregarParametros("fecha", lastMarketDayOfLastMonth.Date.ToShortDateString());
                                cn.AgregarParametros("cotizacion", rows[11].Replace(".", ",").Replace("$ ", ""));
                                cn.AgregarParametros("tipo", tipo);

                                var sqldrUpdateCotizacion = cn.EjecutarSpDataReader("sp_ADM_NewCotizacionReferencia");

                                cn.Cerrar();

                            }
                        }
                    }
                }


                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + quotes + "&f=sl1c1p2n"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %


                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = "";
                for (int i = 0; i < symbols.Length; i++)
                {
                    // Loop through each line from the stream, building the return XML Document string
                    if (symbols[i].Trim() == "")
                        continue;

                    content = strm.ReadLine().Replace("\"", "");
                    string[] contents = content.ToString().Split(',');


                    if (contents[0] != "N/A")
                    {

                        var drow = dtCotizaciones.NewRow();

                        drow["Fecha"] = fechaActualizacion.ToShortDateString();
                        drow["Codigo"] = GetCompleteNameByCode(contents[0]);
                        drow["Tipo"] = tipo;

                        if (tipo == "Indice")
                            drow["Moneda"] = DBNull.Value;
                        else
                            drow["Moneda"] = GetMonedaByCode(contents[0]);


                        if (tipo == "Indice")
                            drow["TIR"] = DBNull.Value;
                        else
                        {

                            var dolarMEP = ObtenerCotizacionDolarMEP();

                            // TIR
                            double tolerance = 0.0001;
                            int maxIters = 100;

                            switch (contents[0].ToString())
                            {

                                case "DIA0.BA":
                                    {

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesDiscount);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "DICA.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesDiscount);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "DICY.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesDiscount);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "DIY0.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesDiscount);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "PAA0.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesPar);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "PARA.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesPar);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "PARY.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesPar);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "PAY0.BA":
                                    {
                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(CuponesPar);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "AO20.BA":
                                    {
                                        List<CashFlow> Cupones = new List<CashFlow>();

                                        cn.Abrir();
                                        cn.AgregarParametros("codigoBono", "AO20");
                                        var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                        var dtCupones = new DataTable();
                                        dtCupones.Load(sqldrCupones);
                                        cn.Cerrar();

                                        for (int j = 0; j < dtCupones.Rows.Count; j++)
                                        {
                                            Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                        }

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(Cupones);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "AA17.BA":
                                    {

                                        List<CashFlow> Cupones = new List<CashFlow>();

                                        cn.Abrir();
                                        cn.AgregarParametros("codigoBono", "AA17");
                                        var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                        var dtCupones = new DataTable();
                                        dtCupones.Load(sqldrCupones);
                                        cn.Cerrar();

                                        for (int j = 0; j < dtCupones.Rows.Count; j++)
                                        {
                                            Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                        }

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(Cupones);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "GJ17.BA":
                                    {

                                        List<CashFlow> Cupones = new List<CashFlow>();

                                        cn.Abrir();
                                        cn.AgregarParametros("codigoBono", "GJ17");
                                        var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                        var dtCupones = new DataTable();
                                        dtCupones.Load(sqldrCupones);
                                        cn.Cerrar();

                                        for (int j = 0; j < dtCupones.Rows.Count; j++)
                                        {
                                            Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                        }

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(Cupones);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "AN18.BA":
                                    {
                                        List<CashFlow> Cupones = new List<CashFlow>();

                                        cn.Abrir();
                                        cn.AgregarParametros("codigoBono", "AN18");
                                        var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                        var dtCupones = new DataTable();
                                        dtCupones.Load(sqldrCupones);
                                        cn.Cerrar();

                                        for (int j = 0; j < dtCupones.Rows.Count; j++)
                                        {
                                            Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                        }

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(Cupones);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                case "AY24.BA":
                                    {
                                        List<CashFlow> Cupones = new List<CashFlow>();

                                        cn.Abrir();
                                        cn.AgregarParametros("codigoBono", "AY24");
                                        var sqldrCupones = cn.EjecutarSpDataReader("sp_ADM_GetCuponesBono");
                                        var dtCupones = new DataTable();
                                        dtCupones.Load(sqldrCupones);
                                        cn.Cerrar();

                                        for (int j = 0; j < dtCupones.Rows.Count; j++)
                                        {
                                            Cupones.Add(new CashFlow(Convert.ToDouble(dtCupones.Rows[j]["MontoPago"].ToString().Replace(".", ",")), Convert.ToDateTime(dtCupones.Rows[j]["FechaPago"])));
                                        }

                                        var cashFlows = new List<CashFlow>();

                                        cashFlows.Add(new CashFlow(Math.Round((Convert.ToDouble(contents[1].Replace(".", ",")) / dolarMEP * -1), 2), DateTime.Now));
                                        cashFlows.AddRange(Cupones);

                                        var irr = Algorithms.CalculateXIRR(cashFlows, tolerance, maxIters);

                                        drow["TIR"] = Math.Round(irr.Value * 100, 2).ToString();

                                    }
                                    break;

                                default:
                                    drow["TIR"] = "-";
                                    break;
                            }

                        }

                        if (contents[1] != "N/A")
                        {
                            try
                            {
                                drow["Valor"] = Math.Round(Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")), 2);
                            }
                            catch
                            {
                                drow["Valor"] = contents[1].Replace(".", ",");
                            }
                        }
                        else
                        {
                            drow["Valor"] = "-";
                        }

                        if (contents[3] != "N/A")
                            drow["CambioDia"] = Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2);
                        else
                            drow["CambioDia"] = "-";

                        if (tipo != "Bono")
                        {
                            if (DateTime.Now.Date > lastMarketDayOfLastMonth.Date)
                                drow["CambioMes"] = Math.Round(((Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")) - valueFirstMarketDayOfMonth[i]) / valueFirstMarketDayOfMonth[i]) * 100, 2);
                            else
                                drow["CambioMes"] = Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2);


                            if (DateTime.Now.Date > lastMarketDayOfLastYear.Date.AddDays(1))
                                drow["CambioAno"] = Math.Round(((Convert.ToDecimal(contents[1].Replace(".", ",").Replace("$ ", "")) - valueFirstMarketDayOfYear[i]) / valueFirstMarketDayOfYear[i]) * 100, 2);
                            else
                                drow["CambioAno"] = Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",").Replace("%", "")), 2);
                        }
                        else
                        {
                            drow["CambioMes"] = "-";
                            drow["CambioAno"] = "-";
                        }

                        dtCotizaciones.Rows.Add(drow);

                    }
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        private double ObtenerCotizacionDolarMEP()
        {
            // Set the return string to null.
            double result = 0.00;
            try
            {
                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=RO15.BA,RO15D.BA,AA17.BA,AA17D.BA,AY24.BA,AY24D.BA&f=l1"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = strm.ReadToEnd().Replace("\n", ",");
                string[] contents = content.ToString().Split(',');

                if (contents[0] != "N/A" && contents[1] != "N/A" && contents[2] != "N/A" && contents[3] != "N/A" && contents[4] != "N/A" && contents[5] != "N/A")
                {
                    result = (Convert.ToDouble(contents[0].Replace(".", ",")) / Convert.ToDouble(contents[1].Replace(".", ",")) + Convert.ToDouble(contents[2].Replace(".", ",")) / Convert.ToDouble(contents[3].Replace(".", ",")) + Convert.ToDouble(contents[4].Replace(".", ",")) / Convert.ToDouble(contents[5].Replace(".", ","))) / 3;
                }
                else
                {

                    // Use Yahoo finance service to download stock data from Yahoo
                    string yahooURL2 = @"http://download.finance.yahoo.com/d/quotes.csv?s=RO15.BA,RO15D.BA,AA17.BA,AA17D.BA,AY24.BA,AY24D.BA&f=p"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                    // Initialize a new WebRequest.
                    HttpWebRequest webreq2 = (HttpWebRequest)WebRequest.Create(yahooURL2);
                    // Get the response from the Internet resource.
                    HttpWebResponse webresp2 = (HttpWebResponse)webreq2.GetResponse();
                    // Read the body of the response from the server.
                    StreamReader strm2 = new StreamReader(webresp2.GetResponseStream(), Encoding.ASCII);


                    string content2 = strm2.ReadToEnd().Replace("\n", ",");
                    string[] contents2 = content2.ToString().Split(',');

                    if (contents2[0] != "N/A" && contents2[1] != "N/A" && contents2[2] != "N/A" && contents2[3] != "N/A" && contents2[4] != "N/A" && contents2[5] != "N/A")
                    {
                        result = (Convert.ToDouble(contents2[0].Replace(".", ",")) / Convert.ToDouble(contents2[1].Replace(".", ",")) + Convert.ToDouble(contents2[2].Replace(".", ",")) / Convert.ToDouble(contents2[3].Replace(".", ",")) + Convert.ToDouble(contents2[4].Replace(".", ",")) / Convert.ToDouble(contents2[5].Replace(".", ","))) / 3;
                    }
                    else
                    {
                        result = 1;
                    }

                }

            }
            catch (Exception ex)
            {

                throw ex;
            }

            return Math.Round(result, 2);

        }


        private decimal ObtenerCotizacionDolarA3500()
        {

            string quandlURL = @"https://www.quandl.com/api/v1/datasets/BCRA/REFER.csv";

            HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(quandlURL);
            // Get the response from the Internet resource.
            HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
            // Read the body of the response from the server.
            StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

            string content = strm.ReadToEnd().Replace("\n", ",");
            string[] contents = content.ToString().Split(',');

            return Math.Round(Convert.ToDecimal(contents[3].Replace(".", ",")), 2);

        }


        private double ObtenerCotizacionCCL()
        {
            // Set the return string to null.
            double result = 0.00;
            try
            {
                string quotes = "";

                if (DateTime.Now < new DateTime(2015, 10, 03))
                    quotes = "RO15.BA,RO15C.BA";
                else
                {

                    if (DateTime.Now < new DateTime(2017, 04, 17))
                        quotes = "AA17.BA,AA17C.BA";
                    else
                    {

                        if (DateTime.Now < new DateTime(2024, 05, 07))
                            quotes = "AY24.BA,AY24C.BA";
                        else
                            return result;
                    }
                }

                // Use Yahoo finance service to download stock data from Yahoo
                string yahooURL = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + quotes + "&f=l1"; // Obtiene el símbolo, último precio, cambio de precio y cambio en %

                // Initialize a new WebRequest.
                HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
                // Get the response from the Internet resource.
                HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
                // Read the body of the response from the server.
                StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);

                string content = strm.ReadToEnd().Replace("\n", ",");
                string[] contents = content.ToString().Split(',');

                if (contents[0] != "N/A" && contents[1] != "N/A")
                {
                    result = Convert.ToDouble(contents[0].Replace(".", ",")) / Convert.ToDouble(contents[1].Replace(".", ","));
                }
                else
                {
                    // Use Yahoo finance service to download stock data from Yahoo
                    string yahooURL2 = @"http://download.finance.yahoo.com/d/quotes.csv?s=" + quotes + "&f=p"; // Obtiene el último precio

                    // Initialize a new WebRequest.
                    HttpWebRequest webreq2 = (HttpWebRequest)WebRequest.Create(yahooURL2);
                    // Get the response from the Internet resource.
                    HttpWebResponse webresp2 = (HttpWebResponse)webreq2.GetResponse();
                    // Read the body of the response from the server.
                    StreamReader strm2 = new StreamReader(webresp2.GetResponseStream(), Encoding.ASCII);


                    string content2 = strm2.ReadToEnd().Replace("\n", ",");
                    string[] contents2 = content2.ToString().Split(',');

                    if (contents2[0] != "N/A" && contents2[1] != "N/A")
                    {
                        result = Convert.ToDouble(contents2[0].Replace(".", ",")) / Convert.ToDouble(contents2[1].Replace(".", ","));
                    }
                    else
                    {
                        result = 1;
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return Math.Round(result, 2);
        }


        private static double ComputeIRR(List<double> cf, int numOfFlows)
        {

            int i = 0, j = 0;
            double old = 0.00;
            double newValue = 0.00;
            double oldguessRate = 0.01;
            double newguessRate = 0.01;
            double guessRate = 0.01;
            double lowGuessRate = 0.01;
            double highGuessRate = 0.5;
            double npv = 0.0;
            double denom = 0.0;

            for (i = 0; i < 1000; i++)
            {
                npv = 0.00;
                for (j = 0; j < numOfFlows; j++)
                {
                    denom = Math.Pow((1 + guessRate), j);
                    npv = npv + (cf[j] / denom);
                }

                /* Stop checking once the required precision is achieved */
                if ((npv > 0) && (npv < 0.00000001))
                    break;
                if (old == 0)
                {
                    old = npv;
                }
                else
                {
                    old = newValue;
                    newValue = npv;
                }
                if (i > 0)
                {
                    if (old < newValue)
                    {
                        if (old < 0 && newValue < 0)
                            highGuessRate = newguessRate;
                        else
                            lowGuessRate = newguessRate;
                    }
                    else
                    {
                        if (old > 0 && newValue > 0)
                            lowGuessRate = newguessRate;
                        else
                            highGuessRate = newguessRate;
                    }
                }
                oldguessRate = guessRate;
                guessRate = (lowGuessRate + highGuessRate) / 2;
                newguessRate = guessRate;
            }
            return guessRate;
        }

        private string GetMonedaByCode(string codigo)
        {

            switch (codigo)
            {

                case "RO15.BA":
                    return "AR$";

                case "RO15D.BA":
                    return "U$S";

                case "AA17.BA":
                    return "AR$";

                case "AA17D.BA":
                    return "U$S";

                case "GJ17.BA":
                    return "AR$";

                case "GJ17D.BA":
                    return "U$S";

                case "AN18.BA":
                    return "AR$";

                case "AN18D.BA":
                    return "U$S";

                case "AY24.BA":
                    return "AR$";

                case "AY24D.BA":
                    return "U$S";

                case "DICA.BA":
                    return "AR$";

                case "DICY.BA":
                    return "AR$";

                case "PARA.BA":
                    return "AR$";                    

                case "ALUA.BA":
                    return "AR$";

                case "ERAR.BA":
                    return "AR$";

                case "APBR.BA":
                    return "AR$";

                case "TS.BA":
                    return "AR$";

                case "YPFD.BA":
                    return "AR$";

                case "PESA.BA":
                    return "AR$";

                case "COME.BA":
                    return "AR$";

                case "GGAL.BA":
                    return "AR$";

                case "BMA.BA":
                    return "AR$";

                case "FRAN.BA":
                    return "AR$";

                case "PAMP.BA":
                    return "AR$";

                case "EDN.BA":
                    return "AR$";

                case "TRAN.BA":
                    return "AR$";

                case "MOLI.BA":
                    return "AR$";

                case "MIRG.BA":
                    return "AR$";

                case "TECO2.BA":
                    return "AR$";

                case "TS":
                    return "U$S";

                case "YPF":
                    return "U$S";

                case "EDN":
                    return "U$S";

                case "PAM":
                    return "U$S";

                case "GGAL":
                    return "U$S";

                case "BMA":
                    return "U$S";

                case "BFR":
                    return "U$S";

                case "TEO":
                    return "U$S";
                case "PBR":
                    return "U$S";


                default:
                    return "-";
            }    
        }

        private string GetCompleteNameByCode(string codigo)
        {

            switch (codigo)
            {
                case "^IXIC":
                    return "Nasdaq";

                case "^GSPC":
                    return "S&P500";

                case "^MERV":
                    return "Merval";

                case "^BVSP":
                    return "Bovespa";

                case "^DJI":
                    return "Dow Jones";

                case "^SSEC":
                    return "Shanghai SE Index";

                case "^IBEX":
                    return "Ibex 35";

                case "^FTSE":
                    return "FTSE";

                case "^GDAXI":
                    return "DAX";

                case "^N225":
                    return "Nikkei 225";

                case "RO15.BA":
                    return "RO15";

                case "RO15D.BA":
                    return "RO15D";

                case "AA17.BA":
                    return "AA17";

                case "AA17D.BA":
                    return "AA17D";

                case "GJ17.BA":
                    return "GJ17";

                case "GJ17D.BA":
                    return "GJ17D";

                case "AN18.BA":
                    return "AN18";

                case "AN18D.BA":
                    return "AN18D";

                case "AY24.BA":
                    return "AY24";

                case "AY24D.BA":
                    return "AY24D";

                case "DICA.BA":
                    return "DICA";

                case "DICY.BA":
                    return "DICY";

                case "PARA.BA":
                    return "PARA";

                case "ALUA.BA":
                    return "ALUA";

                case "ERAR.BA":
                    return "ERAR";

                case "APBR.BA":
                    return "APBR";

                case "TS.BA":
                    return "TS";

                case "YPFD.BA":
                    return "YPFD";

                case "PESA.BA":
                    return "PESA";

                case "COME.BA":
                    return "COME";

                case "GGAL.BA":
                    return "GGAL";

                case "BMA.BA":
                    return "BMA";

                case "FRAN.BA":
                    return "FRAN";

                case "PAMP.BA":
                    return "PAMP";

                case "EDN.BA":
                    return "EDN";

                case "TRAN.BA":
                    return "TRAN";

                case "MOLI.BA":
                    return "MOLI";

                case "MIRG.BA":
                    return "MIRG";

                case "TECO2.BA":
                    return "TECO2";

                case "TS":
                    return "TS (ADR)";

                case "YPF":
                    return "YPF (ADR)";

                case "EDN":
                    return "EDN (ADR)";

                case "PAM":
                    return "PAMP (ADR)";

                case "GGAL":
                    return "GGAL (ADR)";

                case "BMA":
                    return "BMA (ADR)";

                case "BFR":
                    return "FRAN (ADR)";

                case "TEO":
                    return "TECO2 (ADR)";

                case "PBR":
                    return "PBR";


                default:
                    return "-";
            }
        }


    }


    public class CashFlow
    {

        public CashFlow(double amount, DateTime date) { Amount = amount; Date = date; }

        public readonly double Amount;
        public readonly DateTime Date;
    }

    public struct Pair<T, Z>
    {

        public Pair(T first, Z second) { First = first; Second = second; }

        public readonly T First;

        public readonly Z Second;

    }

    public struct AlgorithmResult<TKindOfResult, TValue>
    {

        public AlgorithmResult(TKindOfResult kind, TValue value)
        {

            Kind = kind;
            Value = value;
        }

        public readonly TKindOfResult Kind;
        public readonly TValue Value;
    }

    public enum ApproximateResultKind
    {
        ApproximateSolution,
        ExactSolution,
        NoSolutionWithinTolerance
    }

    public static class Algorithms
    {

        internal static double CalculateXNPV(IEnumerable<CashFlow> cfs, double r)
        {

            if (r <= -1)
                r = -0.99999999; // Very funky ... Better check what an IRR <= -100% means

            return (from cf in cfs
                    let startDate = cfs.OrderBy(cf1 => cf1.Date).First().Date
                    select cf.Amount / Convert.ToDouble(Math.Pow(1 + r, (cf.Date - startDate).Days / 365.0))).Sum();
        }

        internal static Pair<double, double> FindBrackets(Func<IEnumerable<CashFlow>, double, double> func, IEnumerable<CashFlow> cfs)
        {

            // Abracadabra magic numbers ...
            const int maxIter = 100;
            const double bracketStep = 0.5;
            const double guess = 0.1;

            double leftBracket = guess - bracketStep;
            double rightBracket = guess + bracketStep;
            var iter = 0;

            while (func(cfs, leftBracket) * func(cfs, rightBracket) > 0 && iter++ < maxIter)
            {

                leftBracket -= bracketStep;
                rightBracket += bracketStep;
            }

            if (iter >= maxIter)
                return new Pair<double, double>(0, 0);

            return new Pair<double, double>(leftBracket, rightBracket);
        }

        // From "Applied Numerical Analyis" by Gerald
        internal static AlgorithmResult<ApproximateResultKind, double> Bisection(Func<double, double> func, Pair<double, double> brackets, double tol, int maxIters)
        {

            int iter = 1;

            double f3 = 0;
            double x3 = 0;
            double x1 = brackets.First;
            double x2 = brackets.Second;

            do
            {
                var f1 = func(x1);
                var f2 = func(x2);

                if (f1 == 0 && f2 == 0)
                    return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, x1);

                if (f1 * f2 > 0)
                    throw new ArgumentException("x1 x2 values don't bracket a root");

                x3 = (x1 + x2) / 2;
                f3 = func(x3);

                if (f3 * f1 < 0)
                    x2 = x3;
                else
                    x1 = x3;

                iter++;

            } while (Math.Abs(x1 - x2) / 2 > tol && f3 != 0 && iter < maxIters);

            if (f3 == 0)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.ExactSolution, x3);

            if (Math.Abs(x1 - x2) / 2 < tol)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.ApproximateSolution, x3);

            if (iter > maxIters)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, x3);

            throw new Exception("It should never get here");
        }

        public static AlgorithmResult<ApproximateResultKind, double> CalculateXIRR(IEnumerable<CashFlow> cfs, double tolerance, int maxIters)
        {

            var brackets = FindBrackets(CalculateXNPV, cfs);

            if (brackets.First == brackets.Second)
                return new AlgorithmResult<ApproximateResultKind, double>(ApproximateResultKind.NoSolutionWithinTolerance, brackets.First);

            return Bisection(r => CalculateXNPV(cfs, r), brackets, tolerance, maxIters);
        }
    }



}
